package com.personal.dataconvert.util;

import java.io.InputStream;
import java.io.OutputStream;
import java.io.PushbackInputStream;
import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import org.apache.poi.POIXMLDocument;
import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.personal.core.bean.CountEntity;
import com.personal.core.bean.MessageException;
import com.personal.core.data.DataColumn;
import com.personal.core.data.DataColumns;
import com.personal.core.data.DataTable;
import com.personal.core.data.IrregularData;
import com.personal.core.port.HaveDeepAndChildren;
import com.personal.core.utils.CoreUtil;
import com.personal.core.utils.DeepChildrenUtil;
import com.personal.core.utils.Remind;
import com.personal.core.utils.StringUtil;
import com.personal.dataconvert.bean.HeaderConfig;
import com.personal.dataconvert.bean.HtmlConfig;
import com.personal.dataconvert.bean.SheetConfig;

/**
 * Excel，Html 表头相关的工具方法
 * @author cuibo
 */
public class ExcelHtmlUtil
{
    public static final String REPLACESAMECOLUMNFLAG = "$$$$";
    /** 替换原始表头汇总的点的问题 */
    public static final String REPLACEPOINTFLAG = "@SPLIT@";
    /** html放宽至Excel的比例 */
    public static final int RECALWIDTHRATIO = 55;
    /** 不规则报表宽度 */
    public static final int IRREGULARHTMLWIDTH = 60;
    public static final int IRREGULAREXCELWIDTH = ExcelHtmlUtil.RECALWIDTHRATIO * ExcelHtmlUtil.IRREGULARHTMLWIDTH;
    // 不规则表的列
    public static final String DATA = "DATA";
    public static final String ROWINDEX = "ROWINDEX";
    public static final String COLINDEX = "COLINDEX";
    public static final String MERGEROWCOUNT = "MERGEROWCOUNT";
    public static final String MERGECOLCOUNT = "MERGECOLCOUNT";
    public static final String WIDTH = "WIDTH";
    public static final String STYLE = "STYLE";
    public static final String EXCELSTYLE = "EXCELSTYLE";
    public static final String CLICK = "CLICK";
    public static final String ALIGN = "ALIGN";
    public static final String POSTIL = "POSTIL";
    public static final String TDDATAMAP = "TDDATAMAP";
    public static final String CLASS = "CLASS";
    public static final String TRCLASS = "TRCLASS";
    public static final String DATATYPE = "DATATYPE";
    public static final String REDERROR = "REDERROR";
    public static final String BLUEERROR = "BLUEERROR";
    public static final String NULL_TBODY = "<tbody></tbody>";
    public static final String NULL_THEAD = "<thead></thead>";

    /**
     * 计算父节点的宽度
     * @param result
     */
    public static void calculateWidth(List<? extends HeaderConfig> result, Set<String> hidelColumns)
    {
        if (result == null || result.isEmpty())
        {
            return;
        }
        for (HeaderConfig headerConfig : result)
        {
            ExcelHtmlUtil.calculateWidth(headerConfig, hidelColumns);
        }
    }

    /**
     * 根据Datatable创建其表头的HTMl
     * @param dataTable
     * @return
     * @throws Exception
     */
    public static String createHeader(DataTable dataTable) throws Exception
    {
        return ExcelHtmlUtil.createHeader(dataTable, null, null);
    }

    /**
     * 根据Datatable创建其表头的HTMl
     * @param dataTable
     * @param hidelColumns 需要隐藏的列集合，columnName
     * @return
     * @throws Exception
     */
    public static String createHeader(DataTable dataTable, Set<String> hidelColumns) throws Exception
    {
        return ExcelHtmlUtil.createHeader(dataTable, null, hidelColumns);
    }

    /**
     * 根据Datatable创建其表头的HTMl
     * @param dataTable
     * @param splitFlag 表头分割标记。默认是点
     * @return
     * @throws Exception
     */
    public static String createHeader(DataTable dataTable, String splitFlag) throws Exception
    {
        return ExcelHtmlUtil.createHeader(dataTable, splitFlag, null);
    }

    /**
     * 根据Datatable创建其表头的HTMl
     * @param dataTable
     * @param splitFlag 表头分割标记。默认是点
     * @param hidelColumns 需要隐藏的列集合，columnName
     * @return
     * @throws Exception
     */
    public static String createHeader(DataTable dataTable, String splitFlag, Set<String> hidelColumns) throws Exception
    {
        if (dataTable == null || dataTable.getColumns().isEmpty())
        {
            return null;
        }
        // 将strings构建成森林
        List<HeaderConfig> trees = ExcelHtmlUtil.createTrees(dataTable, splitFlag, hidelColumns);
        // 由森林信息构建表头
        return ExcelHtmlUtil.createHeader(trees);
    }

    /**
     * 构建配置表头字符串Html
     * @param trees
     * @param hidelColumns
     * @return
     */
    public static String createHeader(List<? extends HeaderConfig> trees)
    {
        return ExcelHtmlUtil.createHeader(trees, true);
    }

    /**
     * 构建配置表头字符串Html
     * @param trees
     * @param withTitle 是否要title
     * @return
     */
    public static String createHeader(List<? extends HeaderConfig> trees, boolean withTitle)
    {
        // 表头行数即森林中所有节点中最大的深度
        if (trees == null || trees.isEmpty())
        {
            return null;
        }
        int maxRow = ExcelHtmlUtil.getMaxDeepFromTrees(trees);
        StringBuilder result = new StringBuilder();
        result.append("<thead>");
        List<HeaderConfig> rowHeaderConfigs = null;
        int rowspan = 1;
        int deepLength = 1;
        for (int i = 0; i < maxRow; i++)
        {
            result.append("<tr bgcolor=\"#EEF1F6\">");
            // 逐层构建行
            rowHeaderConfigs = ExcelHtmlUtil.getHeaderConfigsByDeepLength(i + 1, trees);
            if (rowHeaderConfigs.size() > 0)
            {
                for (HeaderConfig headerConfig : rowHeaderConfigs)
                {
                    if (!headerConfig.isDisplay())
                    {
                        continue;
                    }
                    // 跨行数。如果当前节点距离子节点中的最大深度差大于等于其相邻的最大深度差,并且其深度差大于0 ，则不跨行
                    rowspan = ExcelHtmlUtil.getMaxDeepLength(rowHeaderConfigs, headerConfig);
                    deepLength = headerConfig.getMaxDeepLength();
                    // cb 2016 11 25 新增隐藏列功能
                    result.append("<th").append(headerConfig.isDisplay() ? "" : " style=\"display:none;\"")
                            .append(withTitle ? " title=\"" + headerConfig.getDisplayName() + "\"" : "")
                            .append(CoreUtil.isEmpty(headerConfig.getStyleClass()) ? "": " class=\"" + headerConfig.getStyleClass() + "\"")
                            .append(" field=\"").append(headerConfig.getValue()).append("\"");
                    if (headerConfig.getWidth() > 0)
                    {
                        result.append(" width=\"").append(headerConfig.getWidth()).append("px\"");
                    }
                    if (deepLength < rowspan && deepLength == 0)
                    {
                        result.append(" rowspan=\"").append(rowspan - deepLength + 1).append("\" colspan=\"")
                        .append(headerConfig.getMaxWidth(new CountEntity())).append("\" ");
                    } else
                    {
                        result.append(" rowspan=\"").append(1).append("\" colspan=\"")
                        .append(headerConfig.getMaxWidth(new CountEntity())).append("\" ");
                    }
                    result.append(" >").append(headerConfig.getDisplayName()).append("</th>");
                }
            }
            result.append("</tr>");
        }
        result.append("</thead>");
        return result.toString();
    }

    /**
     * 将columns信息转成森林
     * @param columns
     * @param splitFlag
     * @param hidelColumns
     * @param dataType
     * @return
     * @throws Exception
     */
    public static List<HeaderConfig> createTrees(DataColumns columns, String splitFlag,
                                                 Set<String> hidelColumns, Map<String, String> dataType) throws Exception
    {
        if (columns == null || columns.size() == 0)
        {
            return null;
        }
        if (CoreUtil.isEmpty(splitFlag))
        {
            // 默认是点
            splitFlag = ".";
        }
        Set<String> columnNames = new HashSet<String>();
        // 列名
        String columnLable = "";
        // 是否是同名
        boolean hassameColumn = false;
        // 列序号
        int index = -1;
        // 结果
        List<HeaderConfig> result = new ArrayList<HeaderConfig>();
        try
        {
            for (DataColumn column : columns)
            {
                index++;
                hassameColumn = false;
                columnLable = column.getColumnLable();
                if (CoreUtil.isEmpty(columnLable))
                {
                    columnLable = column.getColumnName();
                    column.setColumnLable(columnLable);
                }
                if (columnNames.contains(columnLable))
                {
                    hassameColumn = true;
                    column.setColumnLable(columnLable + ExcelHtmlUtil.REPLACESAMECOLUMNFLAG + index);
                } else
                {
                    columnNames.add(columnLable);
                }
                // 多表头
                if (column.getColumnLable().contains(splitFlag))
                {
                    ExcelHtmlUtil.addHeaderConfig(index > 0 ? columns.get(index - 1) : null, column, index, result,
                            splitFlag, hassameColumn, dataType);
                } else
                {
                    HeaderConfig config = null;
                    if (hassameColumn)
                    {
                        config = new HeaderConfig(
                                column.getColumnLable().replace(ExcelHtmlUtil.REPLACESAMECOLUMNFLAG + index, ""),
                                column.getColumnName(), 1, column.getColumnLable(),
                                column.getAlign(), column.getWidth());
                    } else
                    {
                        config = new HeaderConfig(column.getColumnLable(), column.getColumnName(), 1,
                                column.getColumnLable(), column.getAlign(), column.getWidth());
                    }
                    if (!CoreUtil.isEmpty(dataType) && dataType.containsKey(column.getColumnName()))
                    {
                        config.setDataType(dataType.get(column.getColumnName()));
                    }
                    result.add(config);
                }
            }
            // 计算父亲节点的宽度，合并其叶子节点的宽度和（即某个节点的宽度为其叶子节点的宽度和）
            ExcelHtmlUtil.calculateWidth(result, hidelColumns);
        } finally
        {
            // 还原被替换的部分
            index = -1;
            for (DataColumn column : columns)
            {
                index++;
                if (column.getColumnLable().contains(ExcelHtmlUtil.REPLACESAMECOLUMNFLAG + index))
                {
                    column.setColumnLable(
                            column.getColumnLable().replace(ExcelHtmlUtil.REPLACESAMECOLUMNFLAG + index, ""));
                }
            }
        }
        return result;
    }

    public static List<HeaderConfig> createTrees(DataTable dataTable) throws Exception
    {
        return ExcelHtmlUtil.createTrees(dataTable, null);
    }

    public static List<HeaderConfig> createTrees(DataTable dataTable, String splitFlag) throws Exception
    {
        return ExcelHtmlUtil.createTrees(dataTable, splitFlag, null, null);
    }

    public static List<HeaderConfig> createTrees(DataTable dataTable, String splitFlag, Map<String, String> dataType)
            throws Exception
    {
        return ExcelHtmlUtil.createTrees(dataTable, splitFlag, null, dataType);
    }

    public static List<HeaderConfig> createTrees(DataTable table, String splitFlag, Set<String> hidelColumns)
            throws Exception
    {
        return ExcelHtmlUtil.createTrees(table, splitFlag, hidelColumns, null);
    }

    public static List<HeaderConfig> createTrees(DataTable table, String splitFlag, Set<String> hidelColumns,
            Map<String, String> dataType) throws Exception
    {
        if (table == null)
        {
            return null;
        }
        DataColumns columns = table.getColumns();
        return ExcelHtmlUtil.createTrees(columns, splitFlag, hidelColumns, dataType);
    }

    /**
     * 从单元格获取单元格数据内容
     * @param hssfcell
     * @return
     */
    public static String getCellStringValue(Cell cell)
    {
        // 如果该单元格不存在则返回空
        if (null == cell)
        {
            return "";
        }
        // 根据不同的数据类型，对数据进行格式化
        String rValue = "";
        switch (cell.getCellType())
        {
        // 空单元格
        case Cell.CELL_TYPE_BLANK:
            rValue = "";
            break;
        case Cell.CELL_TYPE_BOOLEAN:
            rValue = String.valueOf(cell.getBooleanCellValue());
            break;
        // 数字或日期单元格
        case Cell.CELL_TYPE_NUMERIC:
            if (DateUtil.isCellDateFormatted(cell))
            {
                rValue = String.format("%tF", cell.getDateCellValue());
            } else
            {
                rValue = String.valueOf(cell.getNumericCellValue());
                if (!CoreUtil.isEmpty(rValue))
                {
                    rValue = new BigDecimal(rValue).toPlainString();
                }
                if (rValue.contains("."))
                {
                    if (rValue.endsWith(".0"))
                    {
                        rValue = rValue.substring(0, rValue.lastIndexOf("."));
                    }
                    // 如果小数点后长度大于2,则进行四舍五入取小数点后两位
                    else if (rValue.length() - rValue.lastIndexOf(".") - 1 > 2)
                    {
                        rValue = new BigDecimal(rValue).setScale(2, BigDecimal.ROUND_HALF_UP).toPlainString();
                    }
                }
            }
            break;
        // 字符串单元格
        case Cell.CELL_TYPE_STRING:
            rValue = cell.getStringCellValue();
            break;
        // 公式格式
        case Cell.CELL_TYPE_FORMULA:
            if (Cell.CELL_TYPE_STRING == cell.getCachedFormulaResultType())
            {
                rValue = cell.getStringCellValue();
            } else if (Cell.CELL_TYPE_NUMERIC == cell.getCachedFormulaResultType())
            {
                if (DateUtil.isCellDateFormatted(cell))
                {
                    rValue = String.format("%tF", cell.getDateCellValue());
                } else
                {
                    rValue = String.valueOf(cell.getNumericCellValue());
                    if (!CoreUtil.isEmpty(rValue))
                    {
                        rValue = new BigDecimal(rValue).toPlainString();
                    }
                    if (rValue.contains("."))
                    {
                        if (rValue.endsWith(".0"))
                        { // 如果本来就是不带小数的数字（解析后会自动加上 .0 ）
                          // 去掉 .0 （纯数字单元格可能会解析成带.0的数字，必须去掉.0）
                            rValue = rValue.substring(0, rValue.indexOf("."));
                        }
                        // 如果小数点后长度大于4,则进行四舍五入取小数点后四位
                        if (rValue.length() - rValue.lastIndexOf(".") - 1 > 4)
                        {
                            rValue = new BigDecimal(rValue).setScale(4, BigDecimal.ROUND_HALF_UP).toPlainString();
                        }
                    }
                }
            }
            break;
        default:
            break;
        }
        return rValue;
    }

    /**
     * 自定义颜色
     * @param workbook
     * @param r
     * @param g
     * @param b
     * @return
     */
    public static HSSFColor getColor(Workbook workbook, byte r, byte g, byte b)
    {
        if (!(workbook instanceof HSSFWorkbook))
        {
            return null;
        }
        HSSFPalette palette = ((HSSFWorkbook) workbook).getCustomPalette();
        HSSFColor hssfColor = palette.findColor(r, g, b);
        if (hssfColor == null)
        {
            palette.setColorAtIndex(HSSFColor.LAVENDER.index, r, g, b);
            hssfColor = palette.getColor(HSSFColor.LAVENDER.index);
        }
        return hssfColor;
    }

    /**
     * 获取时间格式化
     * @param formatInfo
     * @return
     */
    public static DateFormat getDateFormat(String formatInfo)
    {
        if (CoreUtil.isEmpty(formatInfo))
        {
            return null;
        }
        return new SimpleDateFormat(formatInfo);
    }

    /**
     * 获取森林中指定深度的所有树
     * @param deepLength
     * @param trees
     * @param result
     * @return
     */
    public static List<HeaderConfig> getHeaderConfigsByDeepLength(int deepLength, List<? extends HeaderConfig> trees)
    {
        List<? extends HaveDeepAndChildren> tempResult = DeepChildrenUtil.getByDeep(trees, deepLength);
        if (tempResult == null || tempResult.isEmpty())
        {
            return null;
        }
        List<HeaderConfig> result = new ArrayList<HeaderConfig>();
        for (HaveDeepAndChildren haveDeepAndChildren : tempResult)
        {
            result.add((HeaderConfig) haveDeepAndChildren);
        }
        return result;
    }

    /**
     * 获取Excel开始行和结束行的字符串表头
     * @param sheet
     * @param start
     * @param end
     * @return
     */
    public static List<String> getHeaders(Sheet sheet, int start, int end)
    {
        List<String> headers = new ArrayList<String>();
        List<Row> rows = ExcelHtmlUtil.getHeadNotEmptyRowNums(start, end, sheet);
        if (rows == null || rows.size() < 1)
        {
            return headers;
        }
        int maxCellCount = rows.get(rows.size() - 1).getLastCellNum();
        for (Row row : rows)
        {
            for (int i = 0; i < row.getLastCellNum(); i++)
            {
                Cell cell = row.getCell(i);
                if (cell != null)
                {
                    String value = ExcelHtmlUtil.getCellStringValue(cell);
                    int rowIndex = cell.getRowIndex();
                    if (ExcelHtmlUtil.isMergedRegion(sheet, cell))
                    {
                        int count = ExcelHtmlUtil.getCellInfo(rowIndex, i, sheet);
                        if (count > 0)
                        {
                            for (int j = 0; j < count - 1; j++)
                            {
                                Cell cell2 = row.getCell(i + j + 1);
                                cell2.setCellValue(value);
                            }
                            i += count - 1;
                        }
                    }
                }
            }
        }
        for (int i = 0; i < maxCellCount; i++)
        {
            StringBuffer str = new StringBuffer();
            for (Row row : rows)
            {
                Cell cell = row.getCell(i);
                if (cell != null && 3 != cell.getCellType())
                {
                    String value = ExcelHtmlUtil.getCellStringValue(cell);
                    if (!CoreUtil.isEmpty(value))
                    {
                        value = value.trim();
                    }
                    str.append(value).append(".");
                }
            }
            if (str.toString().endsWith("."))
            {
                str.delete(str.length() - 1, str.length());
            }
            headers.add(str.toString());
        }
        return headers;
    }

    /**
     * 获取森林的最大深度
     * @param trees
     * @param maxDeep
     * @return
     */
    public static int getMaxDeepFromTrees(List<? extends HeaderConfig> trees)
    {
        return DeepChildrenUtil.getMaxDeep(trees);
    }

    /**
     * 获取除当前节点的相同深度节点中深度的最大值
     * @param trees
     * @param HeaderConfig 当前节点
     * @return
     */
    public static int getMaxDeepLength(List<? extends HeaderConfig> trees, HeaderConfig node)
    {
        int result = 0;
        for (HeaderConfig headerConfig : trees)
        {
            if (headerConfig.equals(node))
            {
                continue;
            }
            int deepLength = headerConfig.getMaxDeepLength();
            if (deepLength > result)
            {
                result = deepLength;
            }
        }
        return result;
    }

    public static Workbook getWorkbook(InputStream inputStream) throws Exception
    {
        if (inputStream == null)
        {
            throw new Exception("文件流不能为空!");
        }
        Workbook workbook = null;
        try
        {
            if (!inputStream.markSupported())
            {
                inputStream = new PushbackInputStream(inputStream, 8);
            }
            if (POIFSFileSystem.hasPOIFSHeader(inputStream))
            {
                workbook = new HSSFWorkbook(inputStream);
            } else if (POIXMLDocument.hasOOXMLHeader(inputStream))
            {
                workbook = new XSSFWorkbook(OPCPackage.open(inputStream));
            }
        } finally
        {
            ExcelHtmlUtil.release(inputStream);
        }
        return workbook;
    }

    /**
     * 处理ExcelSheet页名称问题
     * @param sheetName
     * @return
     */
    public static String handleExcelSheetName(String sheetName)
    {
        if (CoreUtil.isEmpty(sheetName))
        {
            return sheetName;
        }
        while (sheetName.startsWith("'"))
        {
            sheetName = sheetName.substring(1);
        }
        while (sheetName.endsWith("'"))
        {
            sheetName = sheetName.substring(0, sheetName.length() - 1);
        }
        StringBuilder result = new StringBuilder();
        int length = sheetName.length();
        for (int i = 0; i < length; i++)
        {
            char ch = sheetName.charAt(i);
            if (ch == '\\' || ch == '/' || ch == '?' || ch == '*' || ch == '[' || ch == ']')
            {
                continue;
            }
            result.append(ch);
        }
        if (result.length() > 31)
        {
            return result.substring(0, 31);
        }
        return result.toString();
    }

    /**
     * 判断是不是不规则表
     * @param table
     * @return
     */
    public static boolean isIrregular(DataTable table)
    {
        if (!CoreUtil.checkDataTableHasColumns(table))
        {
            return false;
        }
        return table.getColumn(IrregularData.DATA) != null && table.getColumn(IrregularData.COLINDEX) != null
                && table.getColumn(IrregularData.ROWINDEX) != null
                && table.getColumn(IrregularData.COLSPAN) != null
                && table.getColumn(IrregularData.ROWSPAN) != null;
    }

    /**
     * 是否是跨行跨列单元格
     * @param sheet
     * @param cell
     * @return
     */
    public static boolean isMergedRegion(Sheet sheet, Cell cell)
    {
        if (sheet == null || cell == null)
        {
            return false;
        }
        int sheetmergerCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetmergerCount; i++)
        {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstC = ca.getFirstColumn();
            int lastC = ca.getLastColumn();
            int firstR = ca.getFirstRow();
            int lastR = ca.getLastRow();
            if (cell.getColumnIndex() <= lastC && cell.getColumnIndex() >= firstC)
            {
                if (cell.getRowIndex() <= lastR && cell.getRowIndex() >= firstR)
                {
                    return true;
                }
            }
        }
        return false;
    }

    public static String parseStr(Object obj)
    {
        if (obj == null)
        {
            return "";
        }
        if (obj instanceof Date)
        {
            return CoreUtil.formatDate(obj);
        }
        return CoreUtil.parseStr(obj);
    }

    /**
     * 查找森林中某个节点
     * @param trees
     * @param path
     * @return
     */
    public static HeaderConfig querySomeOneNode(List<? extends HeaderConfig> trees, String path)
    {
        if (trees == null || trees.size() == 0 || CoreUtil.isEmpty(path))
        {
            return null;
        }
        HeaderConfig result = null;
        for (HeaderConfig headerConfig : trees)
        {
            if (path.equals(headerConfig.getPath()))
            {
                return headerConfig;
            } else
            {
                if (!headerConfig.getChildren().isEmpty())
                {
                    result = ExcelHtmlUtil.querySomeOneNode(headerConfig.getChildren(), path);
                    if (result != null)
                    {
                        return result;
                    }
                }
            }
        }
        return result;
    }

    /**
     * 查找森林中某个父亲节点
     * @param trees
     * @param splitFlag
     * @param path
     * @return
     */
    public static HeaderConfig querySomeOneParentNode(List<? extends HeaderConfig> trees, String splitFlag, String path)
    {
        if (trees == null || trees.size() == 0 || CoreUtil.isEmpty(path))
        {
            return null;
        }
        HeaderConfig result = null;
        for (HeaderConfig headerConfig : trees)
        {
            if (path.equals(headerConfig.getPath()))
            {
                // 不包含点，则是多表头的根节点
                if (!path.contains(splitFlag) && !headerConfig.isOverHeaderRoot())
                {
                    continue;
                }
                return headerConfig;
            } else
            {
                if (!headerConfig.getChildren().isEmpty())
                {
                    result = ExcelHtmlUtil.querySomeOneParentNode(headerConfig.getChildren(), splitFlag, path);
                    if (result != null)
                    {
                        return result;
                    }
                }
            }
        }
        return result;
    }

    /**
     * 递归给headerConfigs重新计算宽度
     * @param headerConfigs
     */
    public static void recalWidth(List<? extends HeaderConfig> headerConfigs)
    {
        if (headerConfigs == null || headerConfigs.isEmpty())
        {
            return;
        }
        for (HeaderConfig headerConfig : headerConfigs)
        {
            if (headerConfig.getWidth() <= 0)
            {
                headerConfig.setWidth(100);
            }
            // 重新计算宽度
            headerConfig.setWidth(ExcelHtmlUtil.RECALWIDTHRATIO * headerConfig.getWidth());
            if (headerConfig.getChildren() != null && headerConfig.getChildren().size() > 0)
            {
                ExcelHtmlUtil.recalWidth(headerConfig.getChildren());
            }
        }
    }

    /**
     * 关闭输入流
     * @param rs 输入流
     */
    public static void release(InputStream rs)
    {
        if (rs != null)
        {
            try
            {
                rs.close();
            } catch (Exception e)
            {
                rs = null;
                e.printStackTrace();
            }
        }
    }

    /**
     * 关闭输出流
     * @param rs 输出流
     */
    public static void release(OutputStream rs)
    {
        if (rs != null)
        {
            try
            {
                rs.close();
            } catch (Exception e)
            {
                rs = null;
                e.printStackTrace();
            }
        }
    }

    /**
     * 从trees中移除removeHeaderConfig节点
     * @param trees
     * @param removeHeaderConfig
     * @return
     */
    public static boolean removeHeaderConfig(List<? extends HeaderConfig> trees, HeaderConfig removeHeaderConfig)
    {
        if (trees == null || trees.size() == 0 || removeHeaderConfig == null)
        {
            return false;
        }
        for (Iterator<? extends HeaderConfig> iterator = trees.iterator(); iterator.hasNext();)
        {
            HeaderConfig headerConfig = iterator.next();
            if (removeHeaderConfig.equals(headerConfig))
            {
                iterator.remove();
                return true;
            } else
            {
                if (!headerConfig.getChildren().isEmpty())
                {
                    boolean single = ExcelHtmlUtil.removeHeaderConfig(headerConfig.getChildren(), removeHeaderConfig);
                    if (single)
                    {
                        return true;
                    }
                }
            }
        }
        return false;
    }

    /**
     * 获得样式
     * @param cloneStyle
     * @param cra
     * @param sheet
     */
    public static void setBodyStyle(CellStyle cloneStyle, CellRangeAddress cra, Sheet sheet)
    {
        Row rowtemp = null;
        Cell celltemp = null;
        for (int i = cra.getFirstRow(); i <= cra.getLastRow(); i++)
        {
            rowtemp = CellUtil.getRow(i, sheet);
            for (int j = cra.getFirstColumn(); j <= cra.getLastColumn(); j++)
            {
                celltemp = rowtemp.getCell(j);
                if (celltemp == null)
                {
                    celltemp = rowtemp.createCell(j);
                }
                celltemp.setCellStyle(cloneStyle);
            }
        }
    }

    /**
     * 计算HeaderConfig的深度
     * @param sheetConfig
     * @param headerConfigs
     */
    public static void setHtmlConfigAndDeepLength(HtmlConfig htmlConfig, List<? extends HeaderConfig> headerConfigs)
    {
        if (headerConfigs == null || headerConfigs.isEmpty() || htmlConfig == null)
        {
            return;
        }
        htmlConfig.setHeaderConfigs(headerConfigs);
        // 计数器计算深度值
        CountEntity countEntity = null;
        for (HeaderConfig headerConfig : headerConfigs)
        {
            countEntity = new CountEntity(1);
            headerConfig.setDeepLength(countEntity.getCount());
            if (headerConfig.getChildren() != null && headerConfig.getChildren().size() > 0)
            {
                // 计数器加1
                countEntity.add();
                ExcelHtmlUtil.setHtmlConfigAndDeepLength(htmlConfig, headerConfig.getChildren(), countEntity);
            }
        }
    }

    /**
     * 递归给HeaderConfig setSheetConfig
     * @param sheetConfig
     * @param headerConfigs
     */
    public static void setSheetConfig(SheetConfig sheetConfig, List<? extends HeaderConfig> headerConfigs)
    {
        ExcelHtmlUtil.setSheetConfig(sheetConfig, headerConfigs, false);
    }

    /**
     * 递归给HeaderConfig setSheetConfig
     * @param sheetConfig
     * @param headerConfigs
     * @param recalWidth 是否重新计算宽度（HTML转Excel放大）
     */
    public static void setSheetConfig(SheetConfig sheetConfig, List<? extends HeaderConfig> headerConfigs,
            boolean recalWidth)
    {
        ExcelHtmlUtil.setSheetConfig(sheetConfig, headerConfigs, recalWidth, null);
    }

    /**
     * 递归给HeaderConfig setSheetConfig
     * @param sheetConfig
     * @param headerConfigs
     * @param recalWidth 是否重新计算宽度（HTML转Excel放大）
     * @param sameNameCache 同名缓存
     */
    public static void setSheetConfig(SheetConfig sheetConfig, List<? extends HeaderConfig> headerConfigs,
            boolean recalWidth, Map<String, String> sameNameCache)
    {
        if (headerConfigs == null || headerConfigs.isEmpty())
        {
            return;
        }
        for (HeaderConfig headerConfig : headerConfigs)
        {
            headerConfig.setSheetConfig(sheetConfig);
            if (headerConfig.getWidth() <= 0)
            {
                headerConfig.setWidth(100);
            }
            // 重新计算宽度
            if (recalWidth)
            {
                headerConfig.setWidth(ExcelHtmlUtil.RECALWIDTHRATIO * headerConfig.getWidth());
            }
            // 替换同名问题
            if (sameNameCache != null && !sameNameCache.isEmpty())
            {
                String displayName = headerConfig.getDisplayName();
                if (displayName != null)
                {
                    for (Entry<String, String> entry : sameNameCache.entrySet())
                    {
                        if (displayName.contains(entry.getKey()))
                        {
                            displayName = StringUtil.replaceFirst(displayName, entry.getKey(), entry.getValue());
                            headerConfig.setDisplayName(displayName);
                            break;
                        }
                    }
                }
            }
            if (headerConfig.getChildren() != null && headerConfig.getChildren().size() > 0)
            {
                ExcelHtmlUtil.setSheetConfig(sheetConfig, headerConfig.getChildren(), recalWidth, sameNameCache);
            }
        }
    }

    /**
     * 计算HeaderConfig的深度
     * @param sheetConfig
     * @param headerConfigs
     */
    public static void setSheetConfigAndDeepLength(SheetConfig sheetConfig, List<? extends HeaderConfig> headerConfigs)
    {
        if (headerConfigs == null || headerConfigs.isEmpty())
        {
            return;
        }
        sheetConfig.setHeaderConfigs(headerConfigs);
        // 计数器计算深度值
        CountEntity countEntity = null;
        for (HeaderConfig headerConfig : headerConfigs)
        {
            headerConfig.setSheetConfig(sheetConfig);
            countEntity = new CountEntity(1);
            headerConfig.setDeepLength(countEntity.getCount());
            if (headerConfig.getChildren() != null && headerConfig.getChildren().size() > 0)
            {
                // 计数器加1
                countEntity.add();
                ExcelHtmlUtil.setSheetConfigAndDeepLength(sheetConfig, headerConfig.getChildren(), countEntity);
            }
        }
    }

    /**
     * 校验ExcelSheet页名称的合法性
     * @param sheetName
     * @throws MessageException
     */
    public static void validateExcelSheetName(String sheetName) throws MessageException
    {
        Remind.isNotNullOrEmpty(sheetName, "名称不能为空！");
        int length = sheetName.length();
        for (int i = 0; i < length; i++)
        {
            char ch = sheetName.charAt(i);
            if (ch == '\\' || ch == '/' || ch == '?' || ch == '*' || ch == '[' || ch == ']')
            {
                throw new MessageException("名称不能包括\\,/,?,*,[,]符号！");
            }
            if ((i == 0 || i == length - 1) && '\'' == ch)
            {
                throw new MessageException("名称开始和结束符号不能为'！");
            }
        }
    }

    /**
     * column添加森林中的树
     * @param lastColumn
     * @param column
     * @param colIndex
     * @param trees
     * @param splitFlag
     * @param hassameColumn
     * @param dataType
     * @throws Exception
     */
    private static void addHeaderConfig(DataColumn lastColumn, DataColumn column, int colIndex,
            List<HeaderConfig> trees, String splitFlag, boolean hassameColumn, Map<String, String> dataType)
            throws Exception
    {
        if (column == null)
        {
            throw new Exception("构建节点失败，表头信息为空！");
        }
        String headMessage = column.getColumnLable();
        String[] strs = CoreUtil.split(headMessage, splitFlag);
        String str = "";
        // 判断是否有父节点
        HeaderConfig parent = null;
        // 父节点复制
        HeaderConfig tempParent = null;
        // 添加时的父节点
        HeaderConfig addParent = null;
        // 正在添加的节点
        HeaderConfig headerConfig = null;
        // 当前遍历到的路径
        StringBuilder path = new StringBuilder("");
        for (String str2 : strs)
        {
            str = str2;
            if (str == null)
            {
                throw new Exception("存在表头为空的数据！");
            }
            if (CoreUtil.isEmpty(path.toString()))
            {
                path = new StringBuilder(str);
            } else
            {
                path.append(splitFlag).append(str);
            }
            // 如果trees中已经包含了，则去除这个字符串
            parent = ExcelHtmlUtil.querySomeOneParentNode(trees, splitFlag, path.toString());
            // 如果不存在了则直接退出
            if (parent == null)
            {
                break;
            }
            // cb 兼容构建多表头，只支持多表头是相邻的列
            if (lastColumn != null)
            {
                // 如果和上一个column的根部不一致则是新的表头
                String lastColumnLabel = lastColumn.getColumnLable();
                if (!lastColumnLabel.startsWith(parent.getPath()))
                {
                    break;
                }
            }
            tempParent = parent;
            headMessage = StringUtil.replaceFirst(headMessage, str + splitFlag, "");
        }
        // 添加剩余的
        if (headMessage.length() > 0)
        {
            strs = CoreUtil.split(headMessage, splitFlag);
            // 去除已经包含了的节点
            for (int i = 0; i < strs.length; i++)
            {
                str = strs[i];
                // 将最后一个同名的还原
                if (hassameColumn && i == strs.length - 1)
                {
                    str = str.replace(ExcelHtmlUtil.REPLACESAMECOLUMNFLAG + colIndex, "");
                }
                // 构建其本身信息
                if (i == 0)
                {
                    if (tempParent != null)
                    {
                        // 表示存在父节点（path即为上述步骤中没有查找的path）
                        headerConfig = new HeaderConfig(str, column.getColumnName(), tempParent.getDeepLength() + 1,
                                tempParent, path.toString(), column.getAlign(), column.getWidth());
                        if (!CoreUtil.isEmpty(dataType) && dataType.containsKey(column.getColumnName()))
                        {
                            headerConfig.setDataType(dataType.get(column.getColumnName()));
                        }
                        tempParent.addChilds(headerConfig);
                    } else
                    {
                        // 根节点
                        headerConfig = new HeaderConfig(str, column.getColumnName(), i + 1, null, path.toString(),
                                column.getAlign(), column.getWidth());
                        if (!CoreUtil.isEmpty(dataType) && dataType.containsKey(column.getColumnName()))
                        {
                            headerConfig.setDataType(dataType.get(column.getColumnName()));
                        }
                        // 多表头的根节点
                        headerConfig.setOverHeaderRoot(true);
                        trees.add(headerConfig);
                    }
                    addParent = headerConfig;
                } else
                {
                    // 追加path
                    // path += "." + str;
                    path.append(splitFlag + str);
                    headerConfig = new HeaderConfig(str, column.getColumnName(), addParent.getDeepLength() + 1,
                            addParent, path.toString(), column.getAlign(), column.getWidth());
                    if (!CoreUtil.isEmpty(dataType) && dataType.containsKey(column.getColumnName()))
                    {
                        headerConfig.setDataType(dataType.get(column.getColumnName()));
                    }
                    addParent.addChilds(headerConfig);
                    // 父节点更换为当前添加的节点
                    addParent = headerConfig;
                }
            }
        } else
        {
            throw new Exception("存在重复表头信息！");
        }
    }

    /**
     * 汇总父节点宽度并判断是否是隐藏
     * @param headerConfig
     * @param hidelColumns
     */
    private static void calculateWidth(HeaderConfig headerConfig, Set<String> hidelColumns)
    {
        if (headerConfig == null)
        {
            return;
        }
        // 获取所有叶子节点
        List<? extends HeaderConfig> leafs = headerConfig.getLeafHeader();
        if (leafs != null && !leafs.isEmpty())
        {
            int width = 0;
            for (HeaderConfig config : leafs)
            {
                width += config.getWidth();
            }
            headerConfig.setWidth(width);
        }
        // 计算其子节点
        if (headerConfig.getChildren() != null && !headerConfig.getChildren().isEmpty())
        {
            for (HeaderConfig config : headerConfig.getChildren())
            {
                ExcelHtmlUtil.calculateWidth(config, hidelColumns);
            }
        } else
        {
            // 隐藏的列肯定是叶子节点
            if (hidelColumns != null && !hidelColumns.isEmpty() && hidelColumns.contains(headerConfig.getPath()))
            {
                // 不显示
                headerConfig.setDisplay(false);
            }
        }
    }

    private static int getCellInfo(int rowIndex, int colIndex, Sheet sheet)
    {
        int sheetmergerCount = sheet.getNumMergedRegions();
        int colspan = 0;
        for (int i = sheetmergerCount - 1; i >= 0; i--)
        {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            if (ca != null)
            {
                int firstC = ca.getFirstColumn();
                int lastC = ca.getLastColumn();
                int firstR = ca.getFirstRow();
                if (firstC == colIndex && firstR == rowIndex)
                {
                    colspan = lastC - firstC + 1;
                    break;
                }
            }
        }
        return colspan;
    }

    public static List<Row> getHeadNotEmptyRowNums(int start, int end, Sheet sheet)
    {
        List<Row> rows = new ArrayList<Row>();
        for (int i = start; i < end; i++)
        {
            Row row = sheet.getRow(i);
            if (row != null)
            {
                boolean isEmpty = true;
                for (int j = 0; j < row.getLastCellNum(); j++)
                {
                    Cell cell = row.getCell(j);
                    if (cell != null)
                    {
                        if (!CoreUtil.isEmpty(ExcelHtmlUtil.getCellStringValue(cell)))
                        {
                            isEmpty = false;
                            break;
                        }
                    }
                }
                if (!isEmpty)
                {
                    rows.add(row);
                }
            }
        }
        return rows;
    }

    /**
     * 递归计算深度
     * @param htmlConfig
     * @param headerConfigs
     * @param countEntity
     */
    private static void setHtmlConfigAndDeepLength(HtmlConfig htmlConfig, List<? extends HeaderConfig> headerConfigs,
            CountEntity countEntity)
    {
        if (headerConfigs == null || headerConfigs.isEmpty())
        {
            return;
        }
        // 每次都要重新new一个计数器记录原计数器的值
        CountEntity tempCount = null;
        for (HeaderConfig headerConfig : headerConfigs)
        {
            tempCount = new CountEntity();
            tempCount.setCount(countEntity.getCount());
            headerConfig.setDeepLength(tempCount.getCount());
            if (!headerConfig.getChildren().isEmpty())
            {
                // 计数器加1
                tempCount.add();
                // 递归
                ExcelHtmlUtil.setHtmlConfigAndDeepLength(htmlConfig, headerConfig.getChildren(), tempCount);
            }
        }
    }

    /**
     * 递归计算深度
     * @param sheetConfig
     * @param headerConfigs
     * @param countEntity
     */
    private static void setSheetConfigAndDeepLength(SheetConfig sheetConfig, List<? extends HeaderConfig> headerConfigs,
            CountEntity countEntity)
    {
        if (headerConfigs == null || headerConfigs.isEmpty())
        {
            return;
        }
        // 每次都要重新new一个计数器记录原计数器的值
        CountEntity tempCount = null;
        for (HeaderConfig headerConfig : headerConfigs)
        {
            headerConfig.setSheetConfig(sheetConfig);
            tempCount = new CountEntity();
            tempCount.setCount(countEntity.getCount());
            headerConfig.setDeepLength(tempCount.getCount());
            if (!headerConfig.getChildren().isEmpty())
            {
                // 计数器加1
                tempCount.add();
                // 递归
                ExcelHtmlUtil.setSheetConfigAndDeepLength(sheetConfig, headerConfig.getChildren(), tempCount);
            }
        }
    }
}
